package com.augurit.smartform.dao;

import java.math.BigDecimal;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.lob.SerializableClob;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springside.modules.orm.Page;

import com.augurit.common.base.dao.BaseDao;
import com.augurit.common.dao.SysMetaTableDao;
import com.augurit.common.util.lang.StringUtils;
import com.augurit.smartform.constant.PageConstant;
import com.augurit.smartform.database.Table;
import com.augurit.smartform.database.dialect.Dialect;
import com.augurit.smartform.factory.DialectFactory;

/**
 * 智能表单共用DAO扩展类
 */
@Repository
public class SmartFormDao extends BaseDao {
	@Autowired
	private DialectFactory dialectFactory;
	@Autowired
	private SysMetaTableDao sysMetaTableDao;
	
	/**
	 * 查询表单数据放入PAGE中
	 * @param pageSelect
	 * @param page
	 * cuigeqiang
	 */
	public void setPageData(String selectSql,Page<Map> page){
		Session session=sysMetaTableDao.getSession();
		
		String countSql=this.getCountSql(selectSql);
		BigDecimal count=(BigDecimal)session.createSQLQuery(countSql).uniqueResult();
		page.setTotalCount(count.longValue());
		
		String pageSelect=this.getPageSql(page, selectSql);
		Query query=session.createSQLQuery(pageSelect).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map> results=query.list();
		for(Map map : results){
			Iterator it=map.entrySet().iterator();
			while(it.hasNext()){
				Map.Entry entry=(Map.Entry)it.next();
				Object value=entry.getValue();
				if(value instanceof SerializableClob){
					SerializableClob clobValue=(SerializableClob)value;
					String replaceValue=StringUtils.clob2string(clobValue);
					entry.setValue(replaceValue);
				}
				if(entry.getValue() instanceof String){									//展示的时候将字符串转换成HTML展示的格式
					entry.setValue(StringUtils.toHtml(entry.getValue().toString()));
				}
			}
		}
		page.setResult(results);
	}
	
	/**
	 * 查询所有数据不分页
	 * @param selectSql
	 * @param page
	 * cuigeqiang
	 */
	public void setAllPageData(String selectSql,Page<Map> page){
		Session session=sysMetaTableDao.getSession();
		Query query=session.createSQLQuery(selectSql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map> results=query.list();
		for(Map map : results){
			Iterator it=map.entrySet().iterator();
			while(it.hasNext()){
				Map.Entry entry=(Map.Entry)it.next();
				Object value=entry.getValue();
				if(value instanceof SerializableClob){
					SerializableClob clobValue=(SerializableClob)value;
					String replaceValue=StringUtils.clob2string(clobValue);
					entry.setValue(replaceValue);
				}
				if(entry.getValue() instanceof String){									//展示的时候将字符串转换成HTML展示的格式
					entry.setValue(StringUtils.toHtml(entry.getValue().toString()));
				}
			}
		}
		page.setResult(results);
		page.setPageSize(results.size());
		page.setTotalCount(results.size());
		page.setPageNo(1);
	}
	
	/**
	 * 得到唯一的字段序列
	 * @return
	 * cuigeqiang
	 */
	public BigDecimal getFieldId(){
		String sql="SELECT SEQ_SM_FIELDID.NEXTVAL FROM DUAL";
		Session session=this.getSession();
		BigDecimal fieldId=(BigDecimal)session.createSQLQuery(sql).uniqueResult();
		return fieldId;
	}
	
	/**
	 * 得到唯一的页面序列
	 * @return
	 * cuigeqiang
	 */
	public BigDecimal getPageId(){
		String sql="SELECT SEQ_SM_PAGEID.NEXTVAL FROM DUAL";
		Session session=this.getSession();
		BigDecimal fieldId=(BigDecimal)session.createSQLQuery(sql).uniqueResult();
		return fieldId;
	}
	
	/**
	 * 执行SQL集合
	 * @param sqls
	 * cuigeqiang
	 */
	public void executeUpdateList(List<String> sqls){
		Session session=this.getSession();
		for(int i=0;i<sqls.size();i++){
			String sql=sqls.get(i);
			if(StringUtils.isNotBlank(sql)){
				SQLQuery query=session.createSQLQuery(sql);
				query.executeUpdate();
			}
		}
	}
	
	/**
	 * 执行插入SQL语句
	 * @param sqls
	 * @param table
	 * cuigeqiang
	 */
	public void executeInsertSql(List<String> sqls,Table table){
		Session session=this.getSession();
		if(sqls!=null&&sqls.size()>=2){
			String selectNextValueSeq=sqls.get(0);
			String insertSQL=sqls.get(1);
			BigDecimal currVal=(BigDecimal)session.createSQLQuery(selectNextValueSeq).uniqueResult();
			
			Query query=session.createSQLQuery(insertSQL).setBigDecimal(0, currVal);
			query.executeUpdate();
				
			table.setIdValue(currVal.longValue());
		}
	}
	
	/**
	 * 执行拷贝序列的SQL语句
	 * @param sqls
	 * cuigeqiang
	 */
	public void executeCopySequence(List<String> sqls){
		Session session=this.getSession();
		Query query=null;
		if(sqls!=null&&sqls.size()>=3){
			String dropCurrSeq=sqls.get(0);
			query=session.createSQLQuery(dropCurrSeq);
			query.executeUpdate();
			
			String selectCurrSeq=sqls.get(1);
			query=session.createSQLQuery(selectCurrSeq);
			BigDecimal nextVal=(BigDecimal)query.uniqueResult();
			nextVal=nextVal.add(new BigDecimal(1));
			
			String copySequence=sqls.get(2);
			copySequence=copySequence.replace("?", nextVal.toPlainString());
			query=session.createSQLQuery(copySequence);
			query.executeUpdate();
		}
	}
	
	/**
	 * 查询表单数据放入PAGE中
	 * @param pageSelect
	 * @param page
	 * cuigeqiang
	 */
	public void setPageMapData(String selectSql,Map pageMap,Table table){
		Session session=this.getSession();
		
		Query query=session.createSQLQuery(selectSql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		Map result=(Map)query.uniqueResult();
		Iterator it=result.entrySet().iterator();
		while(it.hasNext()){
			Map.Entry entry=(Map.Entry)it.next();
			Object value=entry.getValue();
			if(value instanceof SerializableClob){
				SerializableClob clobValue=(SerializableClob)value;
				String replaceValue=StringUtils.clob2string(clobValue);
				entry.setValue(replaceValue);
			}
			if(entry.getValue() instanceof String){									//展示的时候将字符串转换成HTML展示的格式
				entry.setValue(StringUtils.toHtml(entry.getValue().toString()));
				if(entry.getValue().toString().indexOf(PageConstant.VALUE_SEPERATOR)>=0){
					entry.setValue(entry.getValue().toString().split(PageConstant.VALUE_SEPERATOR));
				}
			}
			String pageMapKey=table.getName().toUpperCase()+"_"+entry.getKey();
			pageMap.put(pageMapKey, entry.getValue());
		}
	}
	
	/**
	 * 查询表单数据放入PAGE中
	 * @param pageSelect
	 * @param page
	 * cuigeqiang
	 */
	public void setPageMapData(String selectSql,Map<String,Object> pageMap){
		Session session=this.getSession();
		
		Query query=session.createSQLQuery(selectSql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		Map result=(Map)query.uniqueResult();
		Iterator it=result.entrySet().iterator();
		while(it.hasNext()){
			Map.Entry entry=(Map.Entry)it.next();
			Object value=entry.getValue();
			if(value instanceof SerializableClob){
				SerializableClob clobValue=(SerializableClob)value;
				String replaceValue=StringUtils.clob2string(clobValue);
				entry.setValue(replaceValue);
			}
			if(entry.getValue() instanceof String){									//展示的时候将字符串转换成HTML展示的格式
				entry.setValue(StringUtils.toHtml(entry.getValue().toString()));
				if(entry.getValue().toString().indexOf(PageConstant.VALUE_SEPERATOR)>=0){
					entry.setValue(entry.getValue().toString().split(PageConstant.VALUE_SEPERATOR));
				}
			}
			String pageMapKey=(String)entry.getKey();
			pageMap.put(pageMapKey, entry.getValue());
		}
	}
	
	/**
	 * 查询数据库表ID
	 * @param selectTableId
	 * @return
	 * cuigeqiang
	 */
	public Long getTableId(String selectTableId){
		Long id=null;
		Session session=this.getSession();
		Query query=session.createSQLQuery(selectTableId);
		BigDecimal result=(BigDecimal)query.uniqueResult();
		if(result!=null){
			id=result.longValue();
		}
		return id;
	}
	
	/**
	 * 查询表单汇总数据放入PAGE中
	 * @param selectSql
	 * @param table
	 * @param summaryMap
	 * cuigeqiang
	 */
	public void setSummaryMapData(String selectSql,Table table,Map<String,Object> summaryMap){
		Map<String,Object> pageMap=new HashMap<String,Object>();
		this.setPageMapData(selectSql, pageMap);
		List<String> summaryColumns=table.getSummaryColumns();
		for(String summaryKey : summaryColumns){
			Iterator<Map.Entry<String, Object>> pageMapIt=pageMap.entrySet().iterator();
			while(pageMapIt.hasNext()){
				Map.Entry<String, Object> mapEntry=pageMapIt.next();
				if(this.isSummary(mapEntry, summaryKey)){
					summaryMap.put(mapEntry.getKey(), mapEntry.getValue());
					break;
				}
			}
		}
	}
	
	/**
	 * 得到查询记录数量的SQL语句
	 * @param selectSql
	 * @return
	 * cuigeqiang
	 */
	public String getCountSql(String selectSql){
		String fromSql = selectSql;
		//select子句与order by子句会影响count查询,进行简单的排除.
		fromSql = "FROM " + StringUtils.substringAfter(fromSql, "FROM");
		fromSql = StringUtils.substringBefore(fromSql, "ORDER BY");

		String countSql = "SELECT COUNT(*) " + fromSql;
		return countSql;
	}
	
	/**
	 * 得到分页的SQL语句
	 * @param page
	 * @param selectSql
	 * @return
	 * cuigeqiang
	 */
	private String getPageSql(Page page,String selectSql){
		Dialect dialect=dialectFactory.getDialect();
		int first=page.getFirst()-1;
		int last=first+page.getPageSize();
		return dialect.getPageString(selectSql, first, last);
	}
	
	/**
	 * 判断是否汇总字段
	 * @param summaryList
	 * @param summaryKey
	 * @return
	 * cuigeqiang
	 */
	private boolean isSummary(Map.Entry<String,Object> mapEntry,String summaryKey){
		boolean flag=false;
		if(mapEntry!=null&&summaryKey!=null){
			if(summaryKey.equalsIgnoreCase(mapEntry.getKey())){
				flag=true;
			}
		}
		return flag;
	}
}
